package org.hgvc.webapp.ets.spring.dao;

import java.util.List;

import org.hgvc.webapp.ets.bo.Agreement;
import org.hgvc.webapp.ets.bo.InvoiceDtl;
import org.hgvc.webapp.ets.bo.RCIAssignmentDetail;
import org.hgvc.webapp.ets.bo.RCIMember;
import org.hgvc.webapp.ets.bo.Voucher;
import org.hgvc.webapp.ets.constant.CommonConstant;
import org.hgvc.webapp.ets.jsf.beans.report.ReportSearchBean;
import org.hibernate.Criteria;
import org.hibernate.criterion.Projection;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.jocsb.base.AbstractHibernateDao;


public class ReportHbmDao extends AbstractHibernateDao {

	public List<Agreement> getAgreementList(ReportSearchBean searchBean)throws Exception{
		Criteria criteria = getSession().createCriteria(Agreement.class);
		if(searchBean.getStartDate()!=null)
			criteria.add(Restrictions.ge("agreetmentDate", searchBean.getStartDate()));
		if(searchBean.getStartDate()!=null)
			criteria.add(Restrictions.le("agreetmentDate", searchBean.getEndDate()));
		return criteria.list();
	}
	
	public List<Voucher> getVoucherList(ReportSearchBean searchBean)throws Exception{
		Criteria criteria = getSession().createCriteria(Voucher.class);
		if(searchBean.getStartDate()!=null){
			if(searchBean.getOperator().equalsIgnoreCase(ReportSearchBean.lt))
				criteria.add(Restrictions.lt("effectiveDate", searchBean.getStartDate()));
			else if(searchBean.getOperator().equalsIgnoreCase(ReportSearchBean.le))
				criteria.add(Restrictions.le("effectiveDate", searchBean.getStartDate()));
			else if(searchBean.getOperator().equalsIgnoreCase(ReportSearchBean.gt))
				criteria.add(Restrictions.gt("effectiveDate", searchBean.getStartDate()));
			else if(searchBean.getOperator().equalsIgnoreCase(ReportSearchBean.ge))
				criteria.add(Restrictions.ge("effectiveDate", searchBean.getStartDate()));
			else 
				criteria.add(Restrictions.eq("effectiveDate", searchBean.getStartDate()));
		}
		return criteria.list();
	}
	
	public List<RCIMember> getRciMemberList(ReportSearchBean searchBean, boolean noExternal)throws Exception{
		Criteria criteria = getSession().createCriteria(RCIMember.class);
		if(noExternal){
			criteria.add(Restrictions.eq("externalFlag", 1));
		}
		if(searchBean.getStartDate()!=null){
			if(searchBean.getOperator().equalsIgnoreCase(ReportSearchBean.lt))
				criteria.add(Restrictions.lt("effectiveDate", searchBean.getStartDate()));
			else if(searchBean.getOperator().equalsIgnoreCase(ReportSearchBean.le))
				criteria.add(Restrictions.le("effectiveDate", searchBean.getStartDate()));
			else if(searchBean.getOperator().equalsIgnoreCase(ReportSearchBean.gt))
				criteria.add(Restrictions.gt("effectiveDate", searchBean.getStartDate()));
			else if(searchBean.getOperator().equalsIgnoreCase(ReportSearchBean.ge))
				criteria.add(Restrictions.ge("effectiveDate", searchBean.getStartDate()));
			else 
				criteria.add(Restrictions.eq("effectiveDate", searchBean.getStartDate()));
		}
		return criteria.list();
	}
	
	public List<RCIAssignmentDetail> getRciAssignmentList(ReportSearchBean searchBean)throws Exception{
		Criteria criteria = getSession().createCriteria(RCIAssignmentDetail.class);
		Criteria rciAssignmentCri = criteria.createCriteria("rciAssignment");
		rciAssignmentCri.createAlias("rciMember", "rciMember");
		rciAssignmentCri.add(Restrictions.eq("rciMember.externalFlag", 1));
		criteria.createAlias("rciBulkBank", "rciBulkBank");
		if(searchBean.getStartDate()!=null){
			if(searchBean.getOperator().equalsIgnoreCase(ReportSearchBean.lt))
				criteria.add(Restrictions.lt("rciBulkBank.effectiveDate", searchBean.getStartDate()));
			else if(searchBean.getOperator().equalsIgnoreCase(ReportSearchBean.le))
				criteria.add(Restrictions.le("rciBulkBank.effectiveDate", searchBean.getStartDate()));
			else if(searchBean.getOperator().equalsIgnoreCase(ReportSearchBean.gt))
				criteria.add(Restrictions.gt("rciBulkBank.effectiveDate", searchBean.getStartDate()));
			else if(searchBean.getOperator().equalsIgnoreCase(ReportSearchBean.ge))
				criteria.add(Restrictions.ge("rciBulkBank.effectiveDate", searchBean.getStartDate()));
			else 
				criteria.add(Restrictions.eq("rciBulkBank.effectiveDate", searchBean.getStartDate()));
		}
		return criteria.list();
	}
	
	public Double getDeposit(Agreement agreement) throws Exception {
		Double deposit = 0.0;
		Criteria criteria = getSession().createCriteria(InvoiceDtl.class);
		criteria.createAlias("invoice", "invoice");
		criteria.add(Restrictions.eq("invoice.refId", agreement.getAgreementId()));
		criteria.add(Restrictions.eq("itemType", CommonConstant.INVOICE_TYPE_DEPOSIT));
		criteria.add(Restrictions.eq("status", CommonConstant.INVOICE_STATUS_PAID));
		criteria.setProjection(Projections.sum("invoice.invoiceAmount"));
		Object depositTemp = criteria.uniqueResult();
		if(depositTemp!=null)
			deposit = (Double) depositTemp;
		return deposit;
	}
}
